<?php

Class SqlDB {

	
//Login details
	var $sqlhost = '' ;
	var $sqluser = '';
	var $sqlpass = '';
	var $sqldatabase = '';
	var $prefix = '';
	var $safemode = '';
	//keep track of what belongs to this connection
	var $connection_id ='';
	var $query_id ='';


	//Constructor - get login info and store it.
	public function __construct($options) {
		$this->sqlhost		= $options['sql_host'];
		$this->sqluser 		= $options['sql_username'];
		$this->sqlpass		= $options['sql_pass'];
		$this->sqldatabase 	= $options['sql_dbname'];
		$this->prefix		= $options['sql_prefix'];
		$this->safemode		= $options['safe_mode'];
		
		$this->connection_id = mysql_connect($this->sqlhost, $this->sqluser, $this->sqlpass) or die("critical error: login wrong");
		mysql_select_db($this->sqldatabase, $this->connection_id) or die ("db not found");
	}
	
	
	// High order methods
	//get_row('user','uid',$this->uid)
	public function get_row($table, $getByColum, $getByValue){
			//Clean all values
			$table = 		$this->cleanString($table);
			$getByColum = 	$this->cleanString($getByColum);
			$getByValue = 	$this->cleanString($getByValue.'');//if its an int, make it string anyways
			//Query database
			$this->query("SELECT * FROM {$this->prefix}{$table} WHERE {$getByColum}='{$getByValue}' LIMIT 1");
			//Query returned no data?
			if($this->countRows()<1) return null;
			//return results
			return $this->fetchRow();// table, get by colum, get by value
	}
	
	public function get_rowcount($table,$colum,$where=null){
		$table = 		$this->cleanString($table);
		$colum = 		$this->cleanString($colum);
		if($where !== null){
				$where_str = 'WHERE ';
				foreach($where as $key => $value){
					$where_str .= " {$this->cleanString($key)} = '{$this->cleanString($value)}' AND";
				}
				$where_str = substr_replace($where_str ,'',-3);
				
			}else{$where_str='';}
			
			$this->query("SELECT {$colum} FROM {$this->prefix}{$table} {$where_str}");
			return $this->countRows();
	
	}
	public function  get_rows($table,$valuesToReturn,$start=0,$limit=50,$where=null){
			if($limit !== 0){
				$start = (int)$start;	//make safe by forcing ints
				$limit = (int)$limit;
				$limits = "LIMIT {$start}, {$limit}";
			} else { $limits=''; };
			
			if($where !== null){
				$where_str = 'WHERE ';
				foreach($where as $key => $value){
					$where_str .= " {$this->cleanString($key)} = '{$this->cleanString($value)}' AND";
				}
				$where_str = substr_replace($where_str ,'',-3);
				
				
			}else{$where_str='';}
			
			//Clean all values
			$table = $this->cleanString($table);
			$getValues='';
			foreach($valuesToReturn as $value){
				$getValues .= ' '.$this->cleanString($value).',';//clean each value and add it to comma seperated string
			}
			//remove last comma
			$getValues = substr_replace($getValues ,'',-1);
		
	//die("SELECT {$getValues} FROM {$this->prefix}{$table} {$where_str} {$limits}");
			//Build query with are safe data
			$this->query("SELECT {$getValues} FROM {$this->prefix}{$table} {$where_str} {$limits}");
			//Return multi level array
			$usrList = array();
			while($usr = $this->fetchRow()){
				$usrList[] = $usr;
			}
			
			return $usrList;
	}
	
	public function set_row($table,$getByColum,$useValue,$keyed_value_array){
	
		//Clean all values
		$table 		= $this->cleanString($table);
		$getByColum = $this->cleanString($getByColum);
		$useValue 	= $this->cleanString($useValue);
		$newdata = '';
		foreach ($keyed_value_array as $key => $newvalue){
			$newdata .= "{$this->cleanString($key)} ='{$this->cleanString($newvalue)}',";
		}
		//remove trailing comma
		$newdata = substr_replace($newdata ,"",-1);

		$this->query("UPDATE {$this->prefix}{$table} SET {$newdata} WHERE {$getByColum}='{$useValue}' LIMIT 1");
		return true;
	}
	
	public function create_row($table,$values_array){
		//clean all values
		$table 		= $this->cleanString($table);
		//make user
		$userdata = '';
		//proccess details
		foreach ($values_array as $newvalue){
			$userdata .= "'{$this->cleanString($newvalue)}',";
		}
		$userdata = substr_replace($userdata ,'',-1);
		
		//go gadget go
		$this->query("INSERT INTO {$this->prefix}{$table} VALUES ({$userdata});");
		return true;
	}
	
	public function remove_row($table,$colum,$removeOnValue){
	
			$table 			= $this->cleanString($table);
			$colum			= $this->cleanString($colum);
			$removeOnValue	= $this->cleanString($removeOnValue);
			//die("DELETE FROM {$table} WHERE {$colum}='{$removeOnValue}' LIMIT 1");
			$this->query("DELETE FROM {$this->prefix}{$table} WHERE {$colum}='{$removeOnValue}' LIMIT 1");//limit removes risk from bad query slightly
			return true;
	}
	
	
	
	//end connect
	public function __destruct(){
		//Although closeing makes sense to me, it causes errors due to what sounds like a bug in the php-mysql
		//shared connection thing. Although, on lookup apparnlty this is just how it works, though my personal thought
		// is this sounds alot like there. Its a feature not a bug, defences for a fail :p
		//mysql_close($this->connection_id);
	}
	//make string database safe
	public function cleanString($str){
		return mysql_real_escape_string($str,$this->connection_id);
	}
	
	//query the database
	public function query($sql)
	{
		$this->query_id = mysql_query($sql,$this->connection_id) or die(mysql_error());
	}

	//get number of returned rows
	public function countRows() {
		return mysql_num_rows($this->query_id);
	}

	//Get rows returned from database
	public function fetchRow() {
		return mysql_fetch_assoc($this->query_id);
	}

	
}
	

